package com.weixin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.function.LongToDoubleFunction;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IBackstageDao;
import com.weixin.util.C3p0Util;
import com.weixin.vo.Admin;
import com.weixin.vo.AdminEdit;
import com.weixin.vo.AdminList;
import com.weixin.vo.Bank;
import com.weixin.vo.Goods;
import com.weixin.vo.GoodsType;
import com.weixin.vo.MemberEdit;
import com.weixin.vo.MemberList;
import com.weixin.vo.MemberType;
import com.weixin.vo.Role;
import com.weixin.vo.XiaoLiang;
/**
 * 后台管理与数据库的链接层
 * @author 唐鑫明
 *
 */
public class BackstageDaoImpl implements IBackstageDao {
	/**
	 * 查询所有的会员信息
	 * 数据表：user_table
	 * @return 返回一个装有会员信息的List集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<MemberEdit> selectAllMember() throws SQLException {
		return C3p0Util.qr.query("select * from user_table",new BeanListHandler<MemberEdit>(MemberEdit.class));
	}
	/**
	 * 根据会员姓名查找对应对应的会员信息
	 * 数据库表:user_table,bank_table,role_table,state_table,acti_table,type_table
	 * @param user_name 会员姓名
	 * @param pageNum 第几页
	 * @param pageSize 每页大小
	 * @return 返回装有会员信息的集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<MemberList> selectMemberInfo(String user_name,int pageNum,int pageSize)throws SQLException {
		List<MemberList> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select  user_table.user_id,user_table.user_name,user_table.user_gender,user_table.user_idcard," + 
										"user_table.user_tel,user_table.user_address,user_table.user_email,user_table.user_ems,bank_table.bank_name," + 
										"user_table.user_cardNo,user_table.user_tuijian,user_table.user_pwd,user_table.user_tpwd," + 
										"state_table.state_name,acti_table.acti_name,user_table.user_time,user_table.user_money," + 
										"role_table.role_name,user_table.integral,type_table.type_name " + 
										"from user_table left join bank_table on user_table.bank_id=bank_table.bank_id " + 
										"left join state_table on user_table.state_id=state_table.state_id " + 
										"left join role_table on user_table.role_id=role_table.role_id " + 
										"left join acti_table on user_table.acti_id=acti_table.acti_id " + 
										"left join type_table on user_table.type_id=type_table.type_id");
		if(user_name==null||user_name.equals("")) {
			sql.append(" limit ?,?");
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<MemberList>(MemberList.class),(pageNum-1)*pageSize,pageSize);
		}else {
			sql.append(" where user_table.user_name like ? limit ?,?");
			String name = "%"+user_name+"%";
			list = C3p0Util.qr.query(sql.toString(),new BeanListHandler<MemberList>(MemberList.class),name,(pageNum-1)*pageSize,pageSize);
		}
		return list;
	}
	/**
	 * 查询一共有多少条会员数据
	 * 数据库表:user_table
	 * @param user_name 会员名
	 * @return 会员数据的条数
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public long selectMemberCount(String user_name) throws SQLException {
		long n = 0;
		if(user_name==null||("".equals(user_name))) {
			n= (long)C3p0Util.qr.query("select count(*) from user_table",new ScalarHandler<>());
		}else {
			String name = "%"+user_name+"%";
			n= (long)C3p0Util.qr.query("select count(*) from user_table where user_name like ?",new ScalarHandler<>(),name);
		}
		return n;
	}
	/**
	 * 根据会员ID修改此会员的状态(启用/禁止)
	 * 数据表:user_table
	 * @param user_id 会员ID
	 * @param state_id 状态ID
	 * @return 修改成功返回true,修改失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editMemberState(int user_id, int state_id) throws SQLException {
		return C3p0Util.qr.update("update user_table set state_id=? where user_id=?",state_id,user_id)>0;
	}
	/**
	 * 根据会员ID查找对应的会员信息
	 * 数据表:user_table,bank_table,role_table,state_table,acti_table,type_table
	 * @param user_id 会员ID
	 * @return 返回会员信息
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public MemberList selectMemberById(int user_id) throws SQLException {
		String sql = "select  user_table.user_id,user_table.user_name,user_table.user_gender,user_table.user_idcard," + 
				"     user_table.user_tel,user_table.user_address,user_table.user_email,user_table.user_ems,bank_table.bank_name," + 
				"	user_table.user_cardNo,user_table.user_tuijian,user_table.user_pwd,user_table.user_tpwd," + 
				"	state_table.state_name,acti_table.acti_name,user_table.user_time,user_table.user_money," + 
				"	role_table.role_name,user_table.integral,type_table.type_name " + 
				"	from user_table left join bank_table on user_table.bank_id=bank_table.bank_id " + 
				"	left join state_table on user_table.state_id=state_table.state_id " + 
				"	left join role_table on user_table.role_id=role_table.role_id " + 
				"	left join acti_table on user_table.acti_id=acti_table.acti_id " + 
				"	left join type_table on user_table.type_id=type_table.type_id where user_table.user_id=?";
		return C3p0Util.qr.query(sql, new BeanHandler<MemberList>(MemberList.class),user_id);
	}
	/**
	 * 根据状态(启用/禁止)名查找对应的状态ID
	 * 数据表:state_table
	 * @param state_name 状态名 
	 * @return 状态ID
	 * @throws SQLException  抛出的异常
	 */
	@Override
	public int selectStateIdByName(String state_name) throws SQLException {
		Connection connection = C3p0Util.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement("select state_id from state_table where state_name=?");
		prepareStatement.setString(1, state_name);
		ResultSet resultSet = prepareStatement.executeQuery();
		resultSet.next();
		int n = resultSet.getInt(1);
		connection.close();
		return n ;
	}
	/**
	 * 查询银行信息
	 * 数据表:bank_table
	 * @return 返回一个装有银行信息的list集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<Bank> selectBankInfo() throws SQLException {
		return C3p0Util.qr.query("select * from bank_table",new BeanListHandler<Bank>(Bank.class));
	}
	/**
	 * 查询会员类型信息
	 * 数据表:type_table
	 * @return 返回一个装有类型信息的list结合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<MemberType> selectTypeInfo() throws SQLException {
		return C3p0Util.qr.query("select * from type_table", new BeanListHandler<MemberType>(MemberType.class));
	}
	/**
	 * 添加会员
	 * 数据表:user-table
	 * @param memberEdit 新添加的会员
	 * @return 添加成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean addMember(MemberEdit memberEdit) throws SQLException {
		String sql = "insert into user_table values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		return C3p0Util.qr.update(sql,memberEdit.getUser_id(),memberEdit.getUser_name(),memberEdit.getUser_gender(),
				memberEdit.getUser_idcard(),memberEdit.getUser_tel(),memberEdit.getUser_address(),memberEdit.getUser_email(),
				memberEdit.getUser_ems(),memberEdit.getBank_id(),memberEdit.getUser_cardNo(),memberEdit.getUser_tuijian(),
				memberEdit.getUser_pwd(),memberEdit.getUser_tpwd(),memberEdit.getState_id(),null,null,null,memberEdit.getActi_id(),
				memberEdit.getUser_time(),memberEdit.getUser_money(),memberEdit.getRole_id(),memberEdit.getIntegral(),memberEdit.getType_id())>0;
	}
	/**
	 * 修改会员信息
	 * 数据表:user_table
	 * @param mEdit  修改后的会员信息
	 * @param user_id 要修改的会员ID
	 * @return 修改成功返回true,修改失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editMember(MemberEdit mEdit,int user_id) throws SQLException {
		String sql = "update user_table set user_name=?,user_gender=?,user_idcard=?,"
				+ "user_tel=?,user_address=?,user_email=?,user_ems=?,bank_id=?,user_cardNo=?," + 
				"user_tuijian=?,user_pwd=?,user_tpwd=?,state_id=?,acti_id=?,user_time=?,"
				+ "type_id=? where user_id=?;";
		return C3p0Util.qr.update(sql,mEdit.getUser_name(),mEdit.getUser_gender(),mEdit.getUser_idcard(),
				mEdit.getUser_tel(),mEdit.getUser_address(),mEdit.getUser_email(),mEdit.getUser_ems(),mEdit.getBank_id(),
				mEdit.getUser_cardNo(),mEdit.getUser_tuijian(),mEdit.getUser_pwd(),mEdit.getUser_tpwd(),mEdit.getState_id(),
				mEdit.getActi_id(),mEdit.getUser_time(),mEdit.getType_id(),user_id)>0;
	}
	/**
	 * 根据会员ID删除用户(删除单个会员)
	 * 数据表:user_table
	 * @param user_id 会员ID 
	 * @return 删除成功返回true,删除失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteMember(int user_id) throws SQLException {
		return C3p0Util.qr.update("delete from user_table where user_id=?",user_id)>0;
	}
	/**
	 * 根据会员ID删除用户(删除多个会员)
	 * 数据表:user_table
	 * @param user_ids 会员ID
	 * @return 删除成功返回true,删除失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteSelectMember(String[] user_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from user_table where user_id in (");
		//创建一个集合存放会员ID
		List<String> valueList =new ArrayList<>();
		for(String user_id:user_ids) {
			sql.append("?,");
			valueList.add(user_id);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return C3p0Util.qr.update(sql.toString(),valueList.toArray() )>0;
	}
	/**
	 * 初始化单个会员的密码
	 * 数据表:user_table
	 * @param user_id 会员ID
	 * @return 密码初始化成功返回true,初始化失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean resetMemberPass(int user_id) throws SQLException {
		return C3p0Util.qr.update("update user_table set user_pwd=123456 where user_id=?",user_id)>0;
	}
	/**
	 * 初始化多个会员的密码
	 * 数据表:user_table
	 * @param user_ids 会员ID
	 * @return 密码初始化成功返回true,初始化失败返回false
	 * @throws SQLException SQLException 抛出的异常
	 */
	@Override
	public boolean resetSelectMemberPass(String[] user_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("update user_table set user_pwd=123456 where user_id in (");
		//创建一个集合存放会员ID
		List<String> valueList =new ArrayList<>();
		for(String user_id:user_ids) {
			sql.append("?,");
			valueList.add(user_id);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return C3p0Util.qr.update(sql.toString(),valueList.toArray() )>0;
	}
	/**
	 * 查询管理员信息
	 * 数据表:admin_table,role_table,state_table
	 * @param admin_name 管理员名
	 * @param pageNum 第几页
	 * @param pageSize 每页大小
	 * @return 返回一个装有admin信息的List集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<AdminList> selectAdminInfo(String admin_name,int pageNum,int pageSize) throws SQLException {
		List<AdminList> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select admin_table.admin_id,admin_table.admin_account,admin_table.admin_name,admin_table.admin_pwd," + 
				"admin_table.admin_img,state_table.state_id,state_table.state_name,role_table.role_id,role_table.role_name, " + 
				"admin_table.admin_create,admin_table.admin_time from admin_table left join role_table on admin_table.role_id=role_table.role_id " + 
				"left join state_table on admin_table.state_id=state_table.state_id");
		if(admin_name==null||admin_name.equals("")) {
			sql.append(" limit ?,?");
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<AdminList>(AdminList.class),(pageNum-1)*pageSize,pageSize);
		}else {
			sql.append(" where admin_name like ? limit ?,?");
			String name = "%"+admin_name+"%";
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<AdminList>(AdminList.class),name,(pageNum-1)*pageSize,pageSize);
		}
		return list;
	}
	/**
	 * 查询一共有多少个管理员
	 * 数据表:admin_table
	 * @param admin_name 管理员名
	 * @return 管理员的人数
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public long selectAdminCount(String admin_name) throws SQLException {
		long n = 0;
		if(admin_name==null||"".equals(admin_name)) {
			n = (long)C3p0Util.qr.query("select count(*) from admin_table", new ScalarHandler<>());
		}else {
			String name = "%"+admin_name+"%";
			n = (long)C3p0Util.qr.query("select count(*) from admin_table where admin_name like ?", new ScalarHandler<>(),name);
		}
		return n;
	}
	/**
	 * 修改管理员的状态(启用/禁用)
	 * 数据表:admin_table
	 * @param admin_id 管理员的ID
	 * @param state_id 状态ID
	 * @return 修改成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editAdminState(int admin_id, int state_id) throws SQLException {
		return C3p0Util.qr.update("update admin_table set state_id=? where admin_id=?", state_id,admin_id)>0;
	}
	/**
	 * 新增管理员
	 * 数据表 admin_table
	 * @param aEdit 新增会员信息
	 * @return 增加成功返回true，否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean addAdmin(AdminEdit aEdit) throws SQLException {
		String sql = "insert into admin_table(admin_account,admin_name,admin_pwd,state_id,role_id,admin_create,admin_time)  values(?,?,?,?,?,?,?)";
		return C3p0Util.qr.update(sql,aEdit.getAdmin_account(),aEdit.getAdmin_name(),
				aEdit.getAdmin_pwd(),aEdit.getState_id(),aEdit.getRole_id(),aEdit.getAdmin_create(),
				new Date())>0;
	}
	/**
	 * 查询角色信息
	 * 数据表:role_table
	 * @return 返回一个装有角色信息的集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<Role> selectRole() throws SQLException {
		return C3p0Util.qr.query("select * from role_table",new BeanListHandler<Role>(Role.class));
	}
	/**
	 * 根据管理员ID查询对应的管理员信息
	 * 数据表:admin_table,role_table,state_table
	 * @param admin_id 管理员ID
	 * @return 返回查询到的管理员信息
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public AdminList selectAdminById(int admin_id) throws SQLException {
		String sql = "select admin_table.admin_id,admin_table.admin_account,admin_table.admin_name,admin_table.admin_pwd," + 
				"admin_table.admin_img,state_table.state_id,state_table.state_name,role_table.role_id,role_table.role_name, " + 
				"admin_table.admin_create,admin_table.admin_time from admin_table left join role_table on admin_table.role_id=role_table.role_id " + 
				"left join state_table on admin_table.state_id=state_table.state_id where admin_table.admin_id=?";
		return C3p0Util.qr.query(sql, new BeanHandler<AdminList>(AdminList.class),admin_id);
	}
	/**
	 * 根据编号修改管理员信息
	 * 数据表:admin_table
	 * @param aEdit 管理员信息
	 * @return 修改成功返回true，否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editAdmin(AdminEdit aEdit) throws SQLException {
		String sql = "update admin_table set admin_account=?,admin_name=?,admin_pwd=?,role_id=?,state_id=?,admin_create=?,admin_time=? where admin_id=?";
		return C3p0Util.qr.update(sql,aEdit.getAdmin_account(),aEdit.getAdmin_name(),aEdit.getAdmin_pwd(),
				aEdit.getRole_id(),aEdit.getState_id(),aEdit.getAdmin_create(),aEdit.getAdmin_time(),aEdit.getAdmin_id())>0;
	}
	/**
	 * 查询管理员信息
	 * 数据表:admin_table
	 * @return 返回一个装有管理员信息的list集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<AdminEdit> selectAdminInfo() throws SQLException {
		return C3p0Util.qr.query("select * from admin_table", new BeanListHandler<AdminEdit>(AdminEdit.class));
	}
	/**
	 * 根据管理员ID删除对应的管理员信息
	 * 数据表:admin_table
	 * @param admin_id 管理员ID
	 * @return 删除成功返回true,删除失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteAdmin(int admin_id) throws SQLException {
		return C3p0Util.qr.update("delete from admin_table where admin_id=?",admin_id)>0;
	}
	/**
	 * 删除选中的管理员
	 * 数据表:admin_table
	 * @param admin_ids 选中管理员的ID
	 * @return 删除成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteSelectAdmin(String[] admin_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from admin_table where admin_id in (");
		//准备一个集合放要删除管理员的ID
		List<String> valueList = new ArrayList<>();
		for(String admin_id:admin_ids) {
			sql.append("?,");
			valueList.add(admin_id);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return C3p0Util.qr.update(sql.toString(),valueList.toArray())>0;
	}
	/**
	 * 根据ID初始化管理的密码
	 * 数据表:admin_table
	 * @param admin_id 管理员ID
	 * @return 初始化成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean resetAdminPass(int admin_id) throws SQLException {
		return C3p0Util.qr.update("update admin_table set admin_pwd=123456 where admin_id=?",admin_id)>0;
	}
	/**
	 * 初始化选中管理员的密码
	 * 数据表:admin_table
	 * @param admin_ids 选中的管理员ID
	 * @return 初始化成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean resetSeletAdminPass(String[] admin_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("update admin_table set admin_pwd=123456 where admin_id in(");
		//准备一个集合放要初始化的管理员ID
		List<String> valueList = new ArrayList<>();
		for(String admin_id:admin_ids) {
			sql.append("?,");
			valueList.add(admin_id);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return C3p0Util.qr.update(sql.toString(),valueList.toArray())>0;
	}
	/**
	 * 查找商品信息
	 * 数据表:goods_table,state_table,type_table,goodstype_table
	 * @param goods_name 商品名
	 * @param goods_id 商品ID
	 * @param pageNum 第几页
	 * @param pageSize 每页大小
	 * @param gtype_id 商品类型
	 * @return 返回一个装有商品信息的list集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<Goods> selectGoodsInfo(String goods_name, String goods_id, int pageNum, int pageSize,int gtype_id) throws SQLException {
		List<Goods> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select goods_table.goods_id,goods_table.goods_name,goods_table.goods_price,goods_table.goods_count,goods_table.state_id," + 
				"state_table.state_name,goods_table.goods_time,goods_table.type_id,type_table.type_name,goods_table.package_id,goods_table.goods_desc,goods_table.goods_img," + 
				"goods_table.gtype_id,goodstype_table.gtype_name " + 
				"from goods_table left join state_table on goods_table.state_id=state_table.state_id " + 
				"left join type_table on goods_table.type_id=type_table.type_id " + 
				"left join goodstype_table on goods_table.gtype_id=goodstype_table.gtype_id");
		if( (goods_name==null||goods_name.equals("") ) && (goods_id==null||goods_id.equals("")) && (gtype_id==0) ) {
			sql.append(" limit ?,?");
			list =  C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),(pageNum-1)*pageSize,pageSize);
			
		}else if( (goods_id!=null)&&(goods_name==null||goods_name.equals("")) && (gtype_id==0) ) {
			sql.append(" where goods_id like ? limit ?,?");
			String id = "%"+goods_id+"%";
			list =  C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),id,(pageNum-1)*pageSize,pageSize);
			
		}else if( (goods_name!=null)&&(goods_id==null||goods_id.equals("")) && (gtype_id==0) ) {
			sql.append(" where goods_name like ? limit ?,?");
			String name = "%"+goods_name+"%";
			list =  C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),name,(pageNum-1)*pageSize,pageSize);
			
		}else if((goods_name==null||goods_name.equals("") ) && (goods_id==null||goods_id.equals("")) && (gtype_id!=0)) {
			sql.append(" where goods_table.gtype_id=? limit ?,?");
			list =  C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),gtype_id,(pageNum-1)*pageSize,pageSize);
			
		}else if((goods_id!=null)&&(goods_name==null||goods_name.equals("")) && (gtype_id!=0)) {
			sql.append(" where goods_id like ? and goods_table.gtype_id=? limit ?,?");
			String id = "%"+goods_id+"%";
			list = C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),id,gtype_id,(pageNum-1)*pageSize,pageSize);
			
		}else if( (goods_name!=null)&&(goods_id==null||goods_id.equals("")) && (gtype_id!=0) ) {
			sql.append(" where goods_name like ? and goods_table.gtype_id=? limit ?,?");
			String name = "%"+goods_name+"%";
			list =  C3p0Util.qr.query(sql.toString(),new BeanListHandler<Goods>(Goods.class),name,gtype_id,(pageNum-1)*pageSize,pageSize);
			
		}
		return list;
	}
	/**
	 * 查询商品数量
	 * 数据表:goods_table
	 * @param goods_id 商品ID
	 * @param goods_name 商品名
	 * @param gtype_id 商品类型
	 * @return 返回商品的数量
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public long selectGoodsCount(String goods_id,String goods_name,int gtype_id) throws SQLException {
		long n = 0;
		if( (goods_id==null||"".equals(goods_id)) && (goods_name==null||"".equals(goods_name))&&(gtype_id==0) ) {
			n = (long)C3p0Util.qr.query("select count(*) from goods_table",new ScalarHandler<>());
		}else if( (goods_id!=null||!("".equals(goods_id))) &&(goods_name==null||"".equals(goods_name))&&(gtype_id==0) ) {
			String id = "%"+goods_id+"%";
			n = (long)C3p0Util.qr.query("select count(*) from goods_table where goods_id like ?",new ScalarHandler<>(),id);
		}else if( (goods_id==null||"".equals(goods_id))&&(goods_name!=null||!("".equals(goods_name)))&&(gtype_id==0) ) {
			String name = "%"+goods_name+"%";
			n = (long)C3p0Util.qr.query("select count(*) from goods_table where goods_name like ?",new ScalarHandler<>(),name);
		}else if( (goods_id==null||"".equals(goods_id)) && (goods_name==null||"".equals(goods_name))&&(gtype_id!=0) ) {
			n = (long)C3p0Util.qr.query("select count(*) from goods_table where goods_table.gtype_id=?",new ScalarHandler<>(),gtype_id);
		}else if( (goods_id!=null||!("".equals(goods_id))) &&(goods_name==null||"".equals(goods_name))&&(gtype_id!=0) ) {
			String id = "%"+goods_id+"%";
			n = (long)C3p0Util.qr.query("select count(*) from goods_table where goods_id like ? and goods_table.gtype_id=?",new ScalarHandler<>(),id,gtype_id);
		}else if((goods_id==null||"".equals(goods_id))&&(goods_name!=null||!("".equals(goods_name)))&&(gtype_id!=0) ) {
			String name = "%"+goods_name+"%";
			n = (long)C3p0Util.qr.query("select count(*) from goods_table where goods_name like ? and goods_table.gtype_id=?",new ScalarHandler<>(),name,gtype_id);
		}
		return n;
	}
	/**
	 * 查询商品类型
	 * 数据表:goodstype_table
	 * @return 返回一个装有商品类型的list集合
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<GoodsType> selectGoodsType() throws SQLException {
		return C3p0Util.qr.query("select * from goodstype_table",new BeanListHandler<GoodsType>(GoodsType.class) );
	}
	/**
	 * 修改商品的状态(上架/下架)
	 * 数据表:goods_table
	 * @param goods_id 商品ID
	 * @param state_id 状态ID
	 * @return 修改成功返回true,否则返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editGoodsState(int goods_id, int state_id) throws SQLException {
		return C3p0Util.qr.update("update goods_table set state_id=? where goods_id=?",state_id,goods_id)>0;
	}
	/**
	 * 查询商品的销量
	 * 数据表:goods_table,sales_table
	 * @param gtype_id 商品种类ID
	 * @return 
	 * @throws SQLException
	 */
	@Override
	public List< Map<String, Object>> selectGoodsSales(int gtype_id) throws SQLException {
		String sql = "select goods_table.goods_name,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve " + 
				"from sales_table left join goods_table on sales_table.goods_id=goods_table.goods_id " + 
				"where goods_table.gtype_id=?";
		return C3p0Util.qr.query(sql, new MapListHandler(),gtype_id);
	}
	/**
	 * 根据商品ID查找对应的商品信息
	 * 数据表:goods_table
	 * @param goods_id 商品ID
	 * @return 返回查找到的商品信息
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public Goods selectGoodsById(int goods_id) throws SQLException {
		String sql = "select goods_table.goods_id,goods_table.goods_name,goods_table.goods_price,goods_table.goods_count,goods_table.state_id,state_table.state_name," + 
				"goods_table.goods_time,goods_table.type_id,type_table.type_name,goods_table.package_id,goods_table.goods_desc,goods_table.goods_img," + 
				"goods_table.gtype_id,goodstype_table.gtype_name " + 
				"from goods_table left join state_table on goods_table.state_id=state_table.state_id " + 
				"left join type_table on goods_table.type_id=type_table.type_id " + 
				"left join goodstype_table on goods_table.gtype_id=goodstype_table.gtype_id " + 
				"where goods_table.goods_id=?";
		return C3p0Util.qr.query(sql, new BeanHandler<Goods>(Goods.class),goods_id);
	}
	/**
	 * 根据商品编号删除对应的商品信息
	 * 数据表:goods_table
	 * @param goods_id 商品ID
	 * @return 删除成功返回true,删除失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteGoodsById(int goods_id) throws SQLException {
		return C3p0Util.qr.update("delete from goods_table where goods_id=?",goods_id)>0;
	}
	/**
	 * 删除选中的商品
	 * 数据表:goods_table
	 * @param goods_ids 选中的商品编号
	 * @return 删除成功返回true,删除失败返回false 
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean deleteSelecedGoods(String[] goods_ids) throws SQLException {
		//准备一个集合存放商品编号
		List<String> valueList = new ArrayList<>();
		StringBuffer sql = new StringBuffer("delete from goods_table where goods_id in (");
		for(String goods_id:goods_ids) {
			sql.append("?,");
			valueList.add(goods_id);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return C3p0Util.qr.update(sql.toString(),valueList.toArray())>0;
	}
	/**
	 * 查询的所有商品的总年销量
	 * 数据表:goods_table,sales_table
	 * @return 返回总年销量
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public int selectTotalGoodsSales() throws SQLException {
		String sql = "select sum(one)+sum(two)+sum(three)+sum(four)+sum(five)+ " + 
				"sum(six)+sum(seven)+sum(eight)+sum(nine)+sum(ten)+sum(eleven)+sum(twelve) total from sales_table";
		Connection connection = C3p0Util.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();
		resultSet.next();
		int n = resultSet.getInt(1);
		connection.close();
		return n;
	}
	/**
	 * 查询的所有商品的总年收入
	 * 数据表:goods_table,sales_table
	 * @return 返回总年收入
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public double selectTotalGoodsPrice() throws SQLException {
		String sql = "select sum(temp2.totalPrice) from (select (one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve)*temp.goods_price totalPrice " + 
				"from sales_table left join (select goods_id,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)temp2";
		Connection connection = C3p0Util.getConnection();
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		ResultSet resultSet = prepareStatement.executeQuery();
		resultSet.next();
		double n = resultSet.getDouble(1);
		connection.close();
		return n;
	}
	/**
	 * 查询销量最高的商品编号，名称，以及年销量
	 * 数据表:goods_table,sales_table
	 * @return 返回销量最高的商品
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public XiaoLiang selectMaxGoodsSales() throws SQLException {
		String sql = "select temp3.goods_id,temp3.goods_name,temp3.xiaoliang " + 
				"from (select temp.goods_id,temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)temp3 where temp3.xiaoliang=(select max(one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve) xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)";
		return C3p0Util.qr.query(sql, new BeanHandler<XiaoLiang>(XiaoLiang.class));
	}
	/**
	 * 查询销量第二的商品编号，名称，以及年销量
	 * 数据表:goods_table,sales_table
	 * @return 返回销量第二的商品
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public XiaoLiang selectSecondGoodsSales() throws SQLException {
		String sql = "select temp3.goods_id,temp3.goods_name,max(temp3.xiaoliang) xiaoliang from (select temp.goods_id,"
				+ "temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id order by xiaoliang desc)temp3 where temp3.xiaoliang<(select "
				+ "max(one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve) xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)";
		return C3p0Util.qr.query(sql, new BeanHandler<XiaoLiang>(XiaoLiang.class));
	}
	/**
	 * 查询销量第三的商品编号，名称，以及年销量
	 * 数据表:goods_table,sales_table
	 * @return 返回销量第三的商品
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public XiaoLiang selectThirdGoodsSales() throws SQLException {
		String sql = "select temp3.goods_id,temp3.goods_name,max(temp3.xiaoliang) xiaoliang from (select temp.goods_id,"
				+ "temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id order by xiaoliang desc)temp3 where temp3.xiaoliang<(select "
				+ "max(temp3.xiaoliang) from (select temp.goods_id,temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id order by xiaoliang desc)temp3 where temp3.xiaoliang<(select "
				+ "max(one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve) xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id))";
		return C3p0Util.qr.query(sql, new BeanHandler<XiaoLiang>(XiaoLiang.class));
	}
	/**
	 * 查询销量第四的商品编号，名称，以及年销量
	 * 数据表:goods_table,sales_table
	 * @return 返回销量第四的商品
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public XiaoLiang selectFourthGoodsSales() throws SQLException {
		String sql = "select temp4.goods_id,temp4.goods_name,max(temp4.xiaoliang) xiaoliang from (select temp.goods_id,"
				+ "temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)temp4 where temp4.xiaoliang<(select max(temp3.xiaoliang) from "
				+ "(select temp.goods_id,temp.goods_name,one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id order by xiaoliang desc)temp3 where temp3.xiaoliang<(select "
				+ "max(temp3.xiaoliang) from (select temp.goods_id,temp.goods_name,one+two+three+four+five+six+seven+"
				+ "eight+nine+ten+eleven+twelve xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id order by xiaoliang desc)temp3 where temp3.xiaoliang<(select "
				+ "max(one+two+three+four+five+six+seven+eight+nine+ten+eleven+twelve) xiaoliang " + 
				"from sales_table left join (select goods_id,goods_name,goods_price,gtype_id from goods_table)temp " + 
				"on sales_table.goods_id=temp.goods_id)))";
		return C3p0Util.qr.query(sql, new BeanHandler<XiaoLiang>(XiaoLiang.class));
	}
	/**
	 * 根据商品编号查询销量排名前四的商品的信息
	 * @param goods_id1 销量第一的商品ID
	 * @param goods_id2 销量第二的商品ID
	 * @param goods_id3 销量第三的商品ID
	 * @param goods_id4 销量第四的商品ID
	 * @return 返回商品信息
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public List<Map<String, Object>> selectGoodsSalesGoodsEchars(int goods_id1, int goods_id2, int goods_id3, int goods_id4)
			throws SQLException {
		String sql = "select goods_table.goods_name,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve " + 
				"	from sales_table left join goods_table on sales_table.goods_id=goods_table.goods_id " + 
				"	where sales_table.goods_id in (?,?,?,?)";
		return C3p0Util.qr.query(sql, new MapListHandler(),goods_id1,goods_id2,goods_id3,goods_id4);
	}
	/**
	 * 修改商品信息
	 * 数据表:goods_table
	 * @param goods 要修改的商品信息
	 * @return 修改成功返回true,修改失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean editGoodsInfo(Goods goods) throws SQLException {
		String sql = "update goods_table set goods_name=?,goods_price=?,goods_count=?,goods_time=?,goods_desc=?,goods_img=?,gtype_id=? where goods_id=?";
		return C3p0Util.qr.update(sql,goods.getGoods_name(),goods.getGoods_price(),goods.getGoods_count(),new Date(),goods.getGoods_desc(),
				goods.getGoods_img(),goods.getGtype_id(),goods.getGoods_id())>0;
	}
	/**
	 * 添加商品
	 * 数据表:goods_table
	 * @param goods 要添加的商品信息 
	 * @return 添加成功返回true，添加失败返回false
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public boolean addGoods(Goods goods) throws SQLException {
		String sql = "insert into goods_table(goods_id,goods_name,goods_price,goods_count,state_id,goods_time,goods_desc,goods_img,gtype_id) values(?,?,?,?,?,?,?,?,?)";
		return C3p0Util.qr.update(sql,goods.getGoods_id(),goods.getGoods_name(),goods.getGoods_price(),goods.getGoods_count(),goods.getState_id(),
				new Date(),goods.getGoods_desc(),goods.getGoods_img(),goods.getGtype_id())>0;
	}
	/**
	 * 根据账号查找数据库中是否存在相同账号的用户
	 * @param admin_account 账号
	 * @return 返回admin
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public Admin selectAdminByAccount(String admin_account) throws SQLException {
		return C3p0Util.qr.query("select admin_account,admin_name,admin_img,admin_pwd from admin_table where admin_account=?",
				new BeanHandler<Admin>(Admin.class),admin_account);
	}
	/**
	 * 在数据库中查找是否存在该商品
	 * 数据表:goods_table
	 * @param goods_id 商品ID
	 * @return 返回goods
	 * @throws SQLException 抛出的异常
	 */
	@Override
	public Goods checkGoods(int goods_id) throws SQLException {
		return C3p0Util.qr.query("select * from goods_table where goods_id=?", new BeanHandler<Goods>(Goods.class),goods_id);
	}
	
	

}
